package com.xx.utils.generator;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author fmy
 * @ClassName: FmyCode
 * @Description:
 * @Date: 2021/8/19 15:44
 * @since JDK 1.8
 */
public class CreateTableUtils {
    static Map<String, String> map = new HashMap<>();

    static {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3308/hn?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true";
        String userName = "root";
        String password = "root";
        map.put("driver", driver);
        map.put("url", url);
        map.put("userName", userName);
        map.put("password", password);
    }


    public static void main(String[] args) {
        try {

            BaseTable baseTable = new BaseTable();
            //表名
            baseTable.setTableName("session_share");
            //表备注
            baseTable.setTableComment("场次分享");

            //强制创建 先删除再创建-----------------
            //baseTable.setConstraintCreate(true);

            // id 0 自增  1 uuid 2 手动输入数字 3手动输入字符串  其他没有id
            baseTable.setId(2);
            baseTable.setIdName("session_id");
            baseTable.setCreateTime(Boolean.TRUE);
            baseTable.setUpdateTime(Boolean.TRUE);

            baseTable.setDel(Boolean.TRUE);

            baseTable.setCreateBy(Boolean.TRUE);
            baseTable.setUpdateBy(Boolean.TRUE); //更新人
            //baseTable.setSort(Boolean.TRUE);   //排序
            //baseTable.setDisplay(Boolean.TRUE);  //是否显示
            baseTable.setDisable(Boolean.TRUE);  //是否禁止
            //baseTable.setRemark(Boolean.TRUE);

            ArrayList<Attribute> attributes = new ArrayList<Attribute>() {{
                add(new Attribute("share_title", SqlType.VARCHAR.setSize("255").toString(), false, null, "分享标题"));
                add(new Attribute("share_poster", SqlType.VARCHAR.setSize("255").toString(), false, null, "分享海报"));
            }};

  /*          ArrayList<Attribute> attributes = new ArrayList<Attribute>() {{
                add(new Attribute("nick_name", "varchar(32)", false, null, "昵称"));
                add(new Attribute("gender", "tinyint", false, "0", "0未知 1男 2女"));
                add(new Attribute("mobile", "varchar(11)", false, null, "手机号"));
                add(new Attribute("id_card", "varchar(18)", false, null, "身份证"));
                add(new Attribute("avatar", "varchar(255)", true, null, "头像地址"));
                add(new Attribute("union_id", "varchar(64)", false, null, "微信UnionId"));
                add(new Attribute("open_id", "varchar(64)", false, null, "微信OpenId"));
            }};   */

      /*      ArrayList<Attribute> attributes = new ArrayList<Attribute>() {{
                add(new Attribute("session_name", "varchar(32)", false, null, "场次名称"));
                add(new Attribute("pre_start_time", "date", true, null, "预计直播开始时间"));
                add(new Attribute("pre_end_time", "date", true, null, "预计直播结束时间"));
                add(new Attribute("anchor_id", "bigint", false, null, "直播主播"));
                add(new Attribute("cover_image", "varchar(255)", true, null, "封面图片"));
                add(new Attribute("virtual_num", "bigint", false, null, "注水人数"));
                add(new Attribute("room_id", "bigint", false, null, "所属直播间id"));
                add(new Attribute("start_time", "date", true, null, "直播开始时间"));
                add(new Attribute("end_time", "date", true, null, "直播结束时间"));
            }};*/


            baseTable.setOtherList(attributes);
            CreateTableUtils.createTable(map, baseTable);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    /**
     * @param map
     * @return void
     * @description:
     * @author: fmy
     * @date: 2022/7/1 11:19
     */
    public static void createTable(Map<String, String> map, BaseTable baseTable) throws SQLException, ClassNotFoundException {
        if (StringUtils.isBlank(baseTable.getTableName())) {
            System.out.println("请输入表名.....");
            return;
        }
        String driver = map.get("driver");
        String url = map.get("url");
        String userName = map.get("userName");
        String password = map.get("password");
        //连接数据库
        Class.forName(driver);
        //测试url中是否包含useSSL字段，没有则添加设该字段且禁用
        if (url.indexOf("?") == -1) {
            url = url + "?useSSL=false";
        } else if (url.indexOf("useSSL=false") == -1 || url.indexOf("useSSL=true") == -1) {
            url = url + "&useSSL=false";
        }
        Connection conn = DriverManager.getConnection(url, userName, password);
        Statement stat = conn.createStatement();
        //获取数据库表名
        ResultSet rs = conn.getMetaData().getTables(null, null, baseTable.getTableName(), null);


        // 先判断是否纯在表名，有则先删除表在创建表
//			stat.executeUpdate("DROP TABLE IF EXISTS sys_admin_divisions;CREATE TABLE sys_admin_divisions("
        StringBuilder sql = new StringBuilder();
        //是否强制创建
        // 判断表是否存在，如果存在则什么都不做，否则创建表
        if (rs.next()) {
            if (baseTable.getConstraintCreate() != null && baseTable.getConstraintCreate()) {
                sql.append("DROP TABLE IF EXISTS ").append(baseTable.getTableName()).append("; ");
            } else {
                return;
            }
        }
        boolean bool = false;
        sql.append("CREATE TABLE " + baseTable.getTableName() + "(");
        if (baseTable.getId() >= 0) {
            if (baseTable.getId() == 1) {
                sql.append("`" + baseTable.getIdName() + "` varchar(32)  NOT NULL COMMENT '主键uuid',");
            } else if (baseTable.getId() == 2) {
                sql.append("`" + baseTable.getIdName() + "` bigint(20) unsigned NOT NULL  COMMENT '主键id',");
            } else if (baseTable.getId() == 3) {
                sql.append("`" + baseTable.getIdName() + "` varchar(20)  NOT NULL COMMENT '主键id',");
            } else {
                sql.append("`" + baseTable.getIdName() + "` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键自增id',");
                bool = true;
            }
        }

        if (!CollectionUtils.isEmpty(baseTable.getOtherList())) {
            String blank = " ";
            String def = "DEFAULT";
            baseTable.getOtherList().forEach(f -> {
                sql.append("`").append(f.getName()).append("` ").append(f.getType()).append(blank);
                if (f.getAllowNull() != null && !f.getAllowNull()) {
                    sql.append(" not null ");
                    if (f.getDef() != null && !f.getDef().equals("null")) {
                        sql.append(def).append(blank).append(f.getDef()).append(blank);
                    }
                } else {
                    sql.append(def).append(blank).append(f.getDef()).append(blank);
                }
                sql.append(" COMMENT '").append(f.getComment()).append("',");
            });
        }
        if (baseTable.getCreateTime()) {
            sql.append("`gmt_create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',");
        }
        if (baseTable.getUpdateTime()) {
            sql.append("`gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间',");
        }
        if (baseTable.getCreateBy()) {
            sql.append("`creator` varchar(32) DEFAULT '' COMMENT '创建人',");
        }
        if (baseTable.getCreateBy()) {
            sql.append("`modifier` varchar(32) DEFAULT '' COMMENT '修改人',");
        }
        if (baseTable.getDel()) {
            sql.append("`del` tinyint(1) DEFAULT 0 COMMENT '逻辑删除 1删除 0未删除',");
        }
        if (baseTable.getDisplay()) {
            sql.append("`display` tinyint(1) DEFAULT 1 COMMENT '是否显示( 0:否 1:是 )',");
        }
        if (baseTable.getDisable()) {
            sql.append("`disable` tinyint(1) DEFAULT 0 COMMENT '状态( 0:正常 1:禁止 )',");
        }
        if (baseTable.getSort()) {
            sql.append("`sort` int(8) DEFAULT 100 COMMENT '排序标识',");
        }
        if (baseTable.getRemark()) {
            sql.append("`remark` varchar(50) DEFAULT '' COMMENT '备注',");
        }
        if (baseTable.getId() >= 0) {
            sql.append("PRIMARY KEY (`" + baseTable.getIdName() + "`)) ");
        }

        sql.append(" ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ");
        if (bool) {
            sql.append(" AUTO_INCREMENT=1 ");
        }
        if (StringUtils.isNotBlank(baseTable.getTableComment())) {
            sql.append("COMMENT='" + baseTable.getTableComment() + "';");
        } else {
            sql.append(";");
        }
        //创建表
        System.out.println(sql);
        stat.executeUpdate(sql.toString());

        // 释放资源
        stat.close();
        conn.close();
    }

    static final Pattern p = Pattern.compile("[\u4e00-\u9fa5]");

    /**
     * 判断是否为汉字
     *
     * @param str 字符串
     * @return
     */
    public static boolean isChineseChar(String str) {

        boolean temp = false;

        Matcher m = p.matcher(str);

        if (m.find()) {
            temp = true;
        }
        return temp;
    }

    /**
     * 字符类型判断
     *
     * @param string
     * @return 1字母, 2数字, 3汉字, 0空格,-1其他
     */
    public static int strMatches(char string) {

        // 判断是否为字母
        if ((string + "").matches("[a-z]") || (string + "").matches("[A-Z]")) {
            return 1;
        }
        // 判断是否为数字
        if ((string + "").matches("[0-9\\+]")) {
            return 2;
        }
        // 判断是否为汉字
        if (isChineseChar(string + "")) {
            return 3;
        }
        // 判断是否为空格
        if ((string + "").matches("[\\s]")) {
            return 0;
        }
        return -1;
    }

    @Data
    static class BaseTable {


        /**
         * 表名
         */
        private String tableName;
        /**
         * id 默认 0 自增  1 uuid 2 手动输入数字 3手动输入字符串  -1没有id
         */
        private Integer id = 0;
        /**
         * 自定义id名称
         */
        private String idName = "id";
        /**
         * 创建时间
         */
        private Boolean createTime = false;
        /**
         * 更新时间
         */
        private Boolean updateTime = false;
        /**
         * 创建人
         */
        private Boolean createBy = false;
        /**
         * 更新人
         */
        private Boolean updateBy = false;
        /**
         * 删除
         */
        private Boolean del = false;
        /**
         * 排序
         */
        private Boolean sort = false;
        /**
         * 是否显示
         */
        private Boolean display = false;
        /**
         * 状态 0 禁止 1正常
         */
        private Boolean disable = false;
        /**
         * 备注
         */
        private Boolean remark = false;
        /**
         * 表备注
         */
        private String tableComment;
        /**
         * 强制创建 先删除在创建
         */
        private Boolean constraintCreate;

        private List<Attribute> otherList;
    }

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    static class Attribute {

        /**
         * 属性名
         */
        private String name;
        /**
         * 类型 int  bigint varchar等
         */
        private String type;
        /**
         * 可以为null
         */
        private Boolean allowNull = true;
        /**
         * 默认值
         */
        private String def = null;
        /**
         * 备注
         */
        private String comment = "";
    }

    enum SqlType {
        TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, VARCHAR, TINYBLOB, TINYTEXT,
        BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LOGNGBLOB, LONGTEXT, DATE, TIME, YEAR, DATETIME, TIMESTAMP;

        private String size;

        public SqlType setSize(String size) {
            this.size = size;
            return this;
        }

        SqlType() {

        }

        SqlType(String size) {
            this.size = size;
        }


        @Override
        public String toString() {
            if (StringUtils.isNotBlank(size)) {
                return this.name() + "(" + size + ")";
            }
            return this.name();
        }
    }
}
